package com.auxgroup.smarthome.utils.excel.reader;

import com.auxgroup.smarthome.utils.excel.model.DataEntity;
import com.auxgroup.smarthome.utils.excel.model.FieldEntity;
import com.auxgroup.smarthome.utils.excel.model.TableEntity;
import com.auxgroup.smarthome.utils.excel.util.DateFormatUtil;
import com.auxgroup.smarthome.utils.excel.util.ExcelReadUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Excel文件读取类，读取Excel表格记录为JAVA对像
 * 
 * @author laiqiuhua
 * 
 */
public class ExcelReader {

	private ExcelReaderConfig excelReaderConfig;
	/**
	 * 创建文件输入流
	 */
	private BufferedReader reader = null;
	/**
	 * 文件二进制输入流
	 */
	private InputStream is = null;
	/**
	 * 当前工作表 sheet
	 */
	private int currSheet;
	/**
	 * 当前位置
	 */
	private int currPosittion;
	/**
	 * 工作表sheet的数量 *
	 */
	private int numOfSheets;
	/**
	 * Wordbook
	 */
	private Workbook workbook = null;

	/**
	 * 由文件输入流创建初始化一个ExcelReader
	 * 
	 * @param inputFile
	 *            文件输入流
	 * @throws IOException
	 * @throws Exception
	 */
	private void initExcelReader(InputStream inputFile, int currPosittion,
			int currSheet) throws IOException, Exception {
		initExcelReader(inputFile);
		// 设置开始行
		this.currPosittion = currPosittion;
		// 设置当前位置
		this.currSheet = currSheet;
	}

	/**
	 * 由文件输入流创建初始化一个ExcelReader
	 * 
	 * @param in
	 *            文件输入流
	 * @throws IOException
	 * @throws Exception
	 */
	private void initExcelReader(InputStream in) throws IOException, Exception {
		if (in == null)
			throw new IOException("文件输入流为空");
		// 设置开始行
		this.currPosittion = 0;
		// 设置当前位置为0
		this.currSheet = 0;

		// 创建文件输入流
//		if (!in.markSupported()) {
//			this.is = new PushbackInputStream(in, 8);// POI reset为8所以这里应该和POI一致
//		}
		workbook = ExcelReadUtil.readExcel(in);
//		try {
//			if (POIFSFileSystem.hasPOIFSHeader(this.is)) { // POI判断是xls格式
//				POIFSFileSystem po = new POIFSFileSystem(this.is);
//				workbook = WorkbookFactory.create(po);
//			} else if (DocumentFactoryHelper.hasOOXMLHeader(this.is)) { // POI判断是xlsx格式
//				OPCPackage pkg = OPCPackage.open(this.is);
//				workbook = WorkbookFactory.create(pkg);
//			} else {
//				throw new InvalidFormatException("POI不支持此Excel版本!");
//			}
//		} catch (Exception e) {
//			throw new Exception("请检查你的Excel格式内容有没有被损坏!");
//		}
		// 设置工作表Sheet数
		this.numOfSheets = this.workbook.getNumberOfSheets();
	}

	/**
	 * 读取Excel表格表头的内容
	 * 
	 * @return String[] 表头内容的数组
	 */
	private String[] readExcelTitle() {
		Sheet sheet = this.getCurrSheet();
		Row row = sheet.getRow(0);
		if (row == null)
			return null;
		// 标题总列数
		int colNum = row.getLastCellNum();

		String[] title = new String[colNum];
		for (int i = 0; i < colNum; i++) {
			title[i] = getCellFormatValue(row.getCell(i));
		}
		return title;
	}

	/**
	 * 读到当前Sheet的数据
	 * 
	 * @return
	 * @throws Exception
	 */
	private List<DataEntity> readSheet() {
		// 根据currSheet值获得当前的工作表Sheet
		Sheet sheet = this.getCurrSheet();
		int lastRowNum = sheet.getLastRowNum();
		// 判断当前行是否到当前工作表sheet的结尾
		List<DataEntity> dataEntityList = new ArrayList<DataEntity>();
		for (int i = 1; i <= lastRowNum; i++) {
			DataEntity dataEntity = new DataEntity();

			List<FieldEntity> fieldEntityList = this.getLine(sheet, i);
			if (fieldEntityList != null) {
				FieldEntity fieldEntity = getFieldEntityKey(fieldEntityList);
				dataEntity.setPkName(fieldEntity.getName());
				dataEntity.setPkVal(fieldEntity.getValue());
				dataEntity.setFieldEntityList(fieldEntityList);

			}
			dataEntityList.add(dataEntity);
		}
		return dataEntityList;
	}

	private FieldEntity getFieldEntityKey(List<FieldEntity> fieldEntityList) {
		for (FieldEntity fieldEntity : fieldEntityList) {
			if (fieldEntity.getIsKey().shortValue() == FieldEntity.IS_KEY
					.shortValue())
				return fieldEntity;
		}
		return null;
	}

	/**
	 * 返回工作表sheet的一行数据
	 * 
	 * @param sheet
	 *            工作表
	 * @param row
	 *            行
	 * @return
	 */
	private List<FieldEntity> getLine(Sheet sheet, int row) {
		// 根据行数取得sheet的一行
		Row rowline = sheet.getRow(row);
		if (rowline == null)
			return null;

		// 获到当前行的列数
		int filledColumns = rowline.getLastCellNum();
		Cell cell = null;
		// 开始读取的列，从第几列开始读。
		int colStart = this.excelReaderConfig.getColStartPosittion();
		List<FieldEntity> list = new ArrayList<FieldEntity>();
		// 遍历所有列
		for (int i = colStart; i < filledColumns; i++) {
			// 取得当前单元格
			cell = rowline.getCell(i);
			String column = this.excelReaderConfig.getColumns()[i - colStart];
			String cellValue = getCellFormatValue(cell);
			FieldEntity fieldEntity = new FieldEntity();
			fieldEntity.setName(column);
			fieldEntity.setValue(cellValue);
			fieldEntity.setIsKey(i == colStart ? FieldEntity.IS_KEY
					: FieldEntity.NOT_KEY);
			list.add(fieldEntity);
		}
		// 返回该行的数据
		return list;
	}

	/**
	 * 取得当前行的值
	 *   (注：改动By poi 3.16)
	 * @param cell
	 * @return
	 */
	private String getCellFormatValue(Cell cell) {
		if (cell == null)
			return "";
		String cellvalue = "";
		// 判断当前单元格的type
		switch (cell.getCellTypeEnum()) {
		case STRING:
			// /取得当前Cell的字符串
			cellvalue = cell.getRichStringCellValue().getString();
			break;

		// 如果当前Cell的type为NUMERIC或者_FORMULA
		case NUMERIC:
			cellvalue = String.format("%.0f", cell.getNumericCellValue());
			break;
		case FORMULA:
			// 判断当前的Cell是否为Date
			if (DateUtil.isCellDateFormatted(cell)) {
				// 如果是在Date类型，则取得该Cell的Date值
				Date date = cell.getDateCellValue();
				cellvalue = DateFormatUtil.format(date);
			} else {
				// 如果是纯数字
				// 取得当前cell的数值
				cellvalue = String.valueOf(cell.getNumericCellValue());
			}
			break;
		case BOOLEAN:
			cellvalue = String.valueOf(cell.getBooleanCellValue());
			break;
		default:
			cellvalue = "";
		}
		return cellvalue;
	}

	/**
	 * 关闭函数执行流的操作
	 */
	public void close() {
		try {
		// 如果is不为空，则关闭InputStream文件输入流
		if (is != null) {
				is.close();
		}
		// 如果reader不为空,则关闭BufferedReader文件输入流
		if (reader != null) {
				reader.close();
		}
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			is = null;
			reader = null;
		}
	}

	private Sheet getCurrSheet() {
		return this.workbook.getSheetAt(this.currSheet);
	}

	public ExcelReaderConfig getExcelReaderConfig() {
		return excelReaderConfig;
	}

	public void setExcelReaderConfig(ExcelReaderConfig excelReaderConfig) {
		this.excelReaderConfig = excelReaderConfig;
	}

	public int getNumOfSheets() {
		return numOfSheets;
	}

	public void setNumOfSheets(int numOfSheets) {
		this.numOfSheets = numOfSheets;
	}

	public void setCurrSheet(int currSheet) {
		this.currSheet = currSheet;
	}

	public int getCurrPosittion() {
		return currPosittion;
	}

	public void setCurrPosittion(int currPosittion) {
		this.currPosittion = currPosittion;
	}

	/**
	 * 获取当前表
	 * 
	 * @param config
	 * @param isMain
	 * @return
	 * @throws Exception
	 */
	private TableEntity getTableEntity(ExcelReaderConfig config, Short isMain) {
		Sheet sheet = this.getCurrSheet();
		String[] columns = this.readExcelTitle();
		if (columns == null)
			return null;
		config.setColumns(columns);
		this.setExcelReaderConfig(config);
		// 读取当前字段
		List<DataEntity> dataEntityList = this.readSheet();

		TableEntity tableEntity = new TableEntity();
		tableEntity.setTitle(columns);
		tableEntity.setName(sheet.getSheetName());
		tableEntity.setIsMain(isMain);
		tableEntity.setDataEntityList(dataEntityList);
		return tableEntity;
	}

	/**
	 * 读取Excel
	 * 
	 * @param input
	 * @return
	 * @throws Exception
	 */
	public TableEntity readFile(InputStream input, int currPosittion,
			int currSheet) throws Exception {
		// 读入Excel文件
		this.initExcelReader(input, currPosittion, currSheet);
		ExcelReaderConfig config = new ExcelReaderConfig();
		TableEntity tableEntity = getTableEntity(config, TableEntity.IS_MAIN);

		int numOfSheets = this.getNumOfSheets();
		if (numOfSheets > 0) {
			List<TableEntity> subList = new ArrayList<TableEntity>();
			for (int i = 1; i < numOfSheets; i++) {
				this.setCurrSheet(i);
				TableEntity table = getTableEntity(config, TableEntity.NOT_MAIN);
				subList.add(table);
			}
			if (subList.size() > 0)
				tableEntity.setSubTableEntityList(subList);
		}
		// 关闭流
		this.close();
		return tableEntity;
	}

	/**
	 * 读取Excel
	 * 
	 * @param input
	 * @return
	 * @throws Exception
	 */
	public TableEntity readFile(InputStream input) throws Exception {
		// 读入Excel文件
		return readFile(input, 0, 0);
	}


	public static void main(String[] args) throws Exception {

		ExcelReader excel = new ExcelReader();

		File file = new File("d:\\1.xls"); // 把testEntity.xls文件复制到d:
		InputStream input = new FileInputStream(file);

		TableEntity excelEntity = excel.readFile(input);
		System.out.println(excelEntity.getDataEntityList().get(0)
				.getFieldEntityList().get(1).getValue());

	}

}
